package user.dao;

import java.awt.Font;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import user.db.DBConnection;
import user.model.Book;
import user.model.Borrow;
import user.model.User;

public class BookDao {
	/**
	 * 图书馆的书籍总和
	 * @return
	 */
	public List<Book> listBooks() {
		String sql = "select * from book";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection con = null;
		con = DBConnection.getConnection();
		List<Book> list = new ArrayList<Book>();
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				String name = rs.getString("name");
				String author = rs.getString("author");
				String type = rs.getString("type");
				float price = rs.getFloat("price");
				int lend = rs.getInt("lend");
				Book book = new Book(name,author,type,price,lend);
				list.add(book);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(rs, ps);
		}
		
		return list;
	}

	/**
	 * 书籍是否被借出
	 * @param bookid
	 * @return
	 */
	public Book SelectBook() {
		String sql = "select * from book where lend = 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		conn = DBConnection.getConnection();
		Book book = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String author = rs.getString("author");
				String type = rs.getString("type");
				int lend = rs.getInt("lend");
				book = new Book(id,name,author,type,lend);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(rs, ps);
		}
		return book;
	}

	/**
	 * 更新图书是否被借阅
	 * @param bookid
	 * @return
	 */
	public int UpdateBookLend(int bookid) {
		String sql = "update book set lend = 0 where id = ?";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, bookid);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return i;
	}
	
		/**
		 * 检查该id的图书是否存在,若存在，返回0，否则返回1
		 * @param con
		 * @param id
		 * @return
		 * @throws Exception
		 */
		public ResultSet bookCheck(Connection con, String id) throws Exception{
			
			//插入图书前 检查bookId是否已经存在，若存在，返回0
			String sqlid = "select * from book where id = ?";		
			PreparedStatement pstmtid = (PreparedStatement) con.prepareStatement(sqlid);	
			pstmtid.setString(1, id);
			ResultSet rs = pstmtid.executeQuery();
			return rs;
		}

	/**添加借阅
	 * @param id
	 * @param bookid
	 * @return
	 */
	public int insertBorrow(String id, int bookid) {
		String sql = "insert into borrow(userid,bookid,btime,stime,times) value(?,?,?,?,?)";
		Connection con = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date(System.currentTimeMillis());
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		calendar.add(Calendar.MONTH, 3);
		java.util.Date utilDate = calendar.getTime();
		 java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, id);
			ps.setInt(2, bookid);
			ps.setDate(3, date);
			ps.setDate(4,sqlDate);
			ps.setInt(5, 0);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return i;
	}
	
	/**
	 * 图书查询
	 * @param connection
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public List<Book> findBook(String name) {
		String sql = "select * from book where name = ? ";
		Connection connection = DBConnection.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Book> bookList = new ArrayList<Book>();
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while(rs.next()) {
				int id = rs.getInt(1);
				String bookName = rs.getString(2);
				String bookauthor = rs.getString(3);
				String type = rs.getString(4);
				Float price = rs.getFloat(5);
				int lend = rs.getInt(6);
				Book book = new Book(id, bookName, bookauthor, type, price, lend);
				bookList.add(book);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBConnection.close(rs, ps);
		}
		return bookList;
		
		
	}
	
	
}